package goodroommate.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import goodroommate.dal.DatabaseSession;
import goodroommate.dao.IDomitoryDao;
import goodroommate.dao.IUserDetailDao;
import goodroommate.domain.Domitory;
import goodroommate.domain.DomitoryAllocation;
import goodroommate.domain.UserDetail;
import goodroommate.exception.DatabaseException;
import goodroommate.exception.ItemAlreadyExistsException;
import goodroommate.exception.ItemNotExistsException;

public class DomitoryDao implements IDomitoryDao {

	IUserDetailDao userDetailDao=new UserDetailDao();
	@Override
	public boolean addDomitoryAllocation(DomitoryAllocation allocation, DatabaseSession dbs)
			throws DatabaseException,ItemAlreadyExistsException {
		String sql="INSERT INTO domitoryallocation (UniversityID,DomitoryID,UserID) VALUES(?,?,?)";
		try{
			if(getMyAllocation(allocation.getUserid(),dbs)==null){
				dbs.executeUpdate(sql,allocation.getUniversityid(),allocation.getDomitoryid(),allocation.getUserid());
				return true;
			}else{
				throw new ItemAlreadyExistsException();
			}
		}catch(DatabaseException e){
			throw new DatabaseException(e);
		}
	}
	
	@Override
	public boolean updateDomitoryAllocation(DomitoryAllocation allocation, DatabaseSession dbs)
			throws DatabaseException{
		String sql="UPDATE domitoryallocation SET UniversityID=?,DomitoryID=? WHERE UserID=?";
		try{
			if(userDetailDao.getUserDetailByUserId(dbs, allocation.getUserid())!=null){
				UserDetail userDetail=new UserDetail();
				userDetail.setUniversity(allocation.getUniversityid());
				userDetail.setDomitory(allocation.getDomitoryid());
				userDetail.setUserid(allocation.getUserid());
				userDetailDao.updateUserDetail(dbs, userDetail);
			}	
			dbs.executeUpdate(sql,allocation.getUniversityid(),allocation.getDomitoryid(),allocation.getUserid());
			return true;		
		}catch(DatabaseException e){
			throw new DatabaseException(e);
		}
	}
	
	@Override
	public DomitoryAllocation getMyAllocation(long userid,DatabaseSession dbs)throws DatabaseException{
		
		String sql="SELECT * FROM domitoryallocation WHERE UserID=?";
		try{
			ResultSet rs=dbs.executeQuery(sql,userid);
			if(rs.next()){
				
				DomitoryAllocation allocation=new DomitoryAllocation();
				allocation.setAllocationid(rs.getInt("AllocationID"));
				allocation.setUniversityid(rs.getInt("UniversityID"));
				allocation.setDomitoryid(rs.getString("DomitoryID"));
				allocation.setUserid(rs.getInt("UserID"));
				return allocation;
			}
			return null;
		}catch(DatabaseException e){
			throw new DatabaseException(e);
		}catch(SQLException e){
			throw new DatabaseException(e);
		}
	}
	
	@Override
	public boolean checkDomitoryCount(DomitoryAllocation allocation, DatabaseSession dbs)
			throws DatabaseException,ItemNotExistsException{
		
		String sql="SELECT * FROM domitories WHERE UniversityID=? AND DomitoryID=?";
		try{
			ResultSet rs=dbs.executeQuery(sql, allocation.getUniversityid(),allocation.getDomitoryid());
			if(rs.next()){
				int count=rs.getInt("Count");
				if(count>0)
					return true;
				else
					return false;
			}else{
				throw new ItemNotExistsException();
			}
		}catch(SQLException e){
			throw new DatabaseException(e);
		}
	}
	
	@Override
	public boolean updateDomitoryCount(DomitoryAllocation allocation, DatabaseSession dbs)
			throws DatabaseException{

		String sql="UPDATE domitories SET Count=Count-1 WHERE UniversityID=? AND DomitoryID=?";
		try{
			dbs.executeUpdate(sql, allocation.getUniversityid(),allocation.getDomitoryid());
			return true;
		}catch(DatabaseException e){
			throw new DatabaseException(e);
		}
	}

	@Override
	public boolean checkDomitoryExistence(Domitory domitory, DatabaseSession dbs) throws DatabaseException {
		String sql="SELECT * FROM domitories WHERE UniversityID=? AND DomitoryID=?";
		try{
			ResultSet rs=dbs.executeQuery(sql, domitory.getUniversityid(),domitory.getDomitoryid());
			if(rs.next())
				return true;
			else
				return false;
		}catch(SQLException e){
			throw new DatabaseException();
		}
	}

	@Override
	public boolean addDomitory(Domitory domitory, DatabaseSession dbs) throws DatabaseException {
		
		String sql="INSERT INTO domitories(UniversityID,DomitoryID,Count)VALUES(?,?,?)";
		try{
			dbs.executeUpdate(sql, domitory.getUniversityid(),domitory.getDomitoryid(), domitory.getCount());
			return true;
		}catch(DatabaseException e){
			throw new DatabaseException(e);
		}

	}

	@Override
	public ArrayList<DomitoryAllocation> getMyRoommates(DomitoryAllocation myallocation, DatabaseSession dbs)
			throws DatabaseException {
		
		String sql="SELECT domitoryallocation.*,universities.Name,users.Username "
				+ "FROM domitoryallocation,universities,users "
				+ "WHERE domitoryallocation.UniversityID=universities.ID AND "
				+ "domitoryallocation.UserID=users.ID AND "
				+ "domitoryallocation.UniversityID=? AND "
				+ "domitoryallocation.DomitoryID=? AND "
				+ "UserID!=?";
		ArrayList<DomitoryAllocation>rst=new ArrayList<DomitoryAllocation>();
		try{
			ResultSet rs=dbs.executeQuery(sql,myallocation.getUniversityid(),myallocation.getDomitoryid(),myallocation.getUserid());
			while(rs.next()){
				
				DomitoryAllocation allocation=new DomitoryAllocation();
				allocation.setAllocationid(rs.getInt("AllocationID"));
				allocation.setUniversityid(rs.getInt("UniversityID"));
				allocation.setUniversityname(rs.getString("Name"));
				allocation.setDomitoryid(rs.getString("DomitoryID"));
				allocation.setUserid(rs.getInt("UserID"));
				allocation.setUsername(rs.getString("Username"));
				rst.add(allocation);
			}
			return rst;
		}catch(DatabaseException e){
			throw new DatabaseException(e);
		}catch(SQLException e){
			throw new DatabaseException(e);
		}
	}

	@Override
	public ArrayList<Domitory> getDomitory(int type, DatabaseSession dbs) throws DatabaseException {
		String sql = "SELECT domitories.*,universities.Name FROM domitories,universities "
				+ "WHERE universities.ID=domitories.UniversityID ";
		List<Object> params = new LinkedList<>();
		if (type != 0) {
			sql += "AND Tag=? ";
			params.add(type);
		}
		sql += "GROUP BY UniversityID,DomitoryID";
		ArrayList<Domitory> rst = new ArrayList<Domitory>();
		try {
			ResultSet rs = dbs.executeQuery(sql, params);
			while (rs.next()) {
				Domitory domitory = new Domitory();
				domitory.setUniversityid(rs.getInt("UniversityID"));
				domitory.setUniversityname(rs.getString("Name"));
				domitory.setDomitoryid(rs.getString("DomitoryID"));
				domitory.setCount(rs.getInt("Count"));
				domitory.setTag(rs.getInt("Tag"));
				rst.add(domitory);
			}
			return rst;

		} catch (SQLException e) {
			throw new DatabaseException();
		}
	}

	@Override
	public ArrayList<DomitoryAllocation> getDomitoryAllocationByDomitory(Domitory domitory, DatabaseSession dbs)
			throws DatabaseException {
		ArrayList<DomitoryAllocation> rst = new ArrayList<DomitoryAllocation>();
		String sql = "SELECT domitoryallocation.* ,universities.Name,users.Username "
				+ "FROM domitoryallocation,universities,users "
				+ "WHERE domitoryallocation.UniversityID=universities.ID AND domitoryallocation.UserID=users.ID "
				+ "AND UniversityID=? " + "AND DomitoryID=?";
		try {
			ResultSet rs = dbs.executeQuery(sql, domitory.getUniversityid(), domitory.getDomitoryid());
			while (rs.next()) {

				DomitoryAllocation allocation = new DomitoryAllocation();
				allocation.setAllocationid(rs.getInt("AllocationID"));
				allocation.setUniversityid(rs.getInt("UniversityID"));
				allocation.setUniversityname(rs.getString("Name"));
				allocation.setDomitoryid(rs.getString("DomitoryID"));
				allocation.setUserid(rs.getInt("UserID"));
				allocation.setUsername(rs.getString("Username"));
				rst.add(allocation);
			}
			return rst;
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}
	}

	@Override
	public int[][] getDomitoryPreference(DatabaseSession dbs) throws DatabaseException {

		String sql="SELECT * FROM domitorypreference";
		try {
			ResultSet rs = dbs.executeQuery(sql);
			rs.last();
			int[][] results = new int[rs.getRow()][6];
			int i = 0;
			if (!rs.isFirst()) {
				rs.first();
				results[0][0] = rs.getInt("UserID");
				results[0][1] = rs.getInt("DomitoryCount");
				results[0][2] = rs.getInt("DomitoryNoisy");
				results[0][3] = rs.getInt("DomitoryLayer");
				results[0][4] = rs.getInt("DomitoryYear");
				results[0][5] = rs.getInt("DomitoryTag");
				i++;
			}
			while (rs.next()) {
				results[i][0] = rs.getInt("UserID");
				results[i][1] = rs.getInt("DomitoryCount");
				results[i][2] = rs.getInt("DomitoryNoisy");
				results[i][3] = rs.getInt("DomitoryLayer");
				results[i][4] = rs.getInt("DomitoryYear");
				results[i][5] = rs.getInt("DomitoryTag");
				i++;
			}
			return results;
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}
	}

	@Override
	public ArrayList<Integer> getDistinctDomitoryTay(DatabaseSession dbs) throws DatabaseException {
		String sql="SELECT DISTINCT Tag FROM domitories";
		ArrayList<Integer> tags = new ArrayList<Integer>();
		try {
				ResultSet rs = dbs.executeQuery(sql);
				while (rs.next()) {
					tags.add(rs.getInt("Tag"));
				}
				return tags;
		} catch (SQLException e) {
			throw new DatabaseException(e);
		}
	}

}
